<?php

require_once 'Poster.php';

class OPDatabase {

	/**
	* -------------------------------------------------------------------------
	*
	* 									Const
	*
	* -------------------------------------------------------------------------
	*/
	
	/**
	* Database host
	*/
	const DB_HOST = 'localhost';
	
	/**
	 * Database name
	 */
	const DB_NAME = 'Posters';
	
	/**
	*  Database user name
	*/
	const DB_USER = 'root';
	
	/**
	*  Database password
	*/
	const DB_PASSWORD = '';
	
	/**
	* -------------------------------------------------------------------------
	*
	* 									Attributes
	*
	* -------------------------------------------------------------------------
	*/
	
	/**
	 * Database
	 */
	protected static $database;
	
	/**
	 * -------------------------------------------------------------------------
	 *
	 * 									Static
	 *
	 * -------------------------------------------------------------------------
	 */
	
	/**
	 * Database connexion
	 */
	public static function connect() {
		self::$database = new PDO('mysql:host=' . self::DB_HOST . ';dbname=' . self::DB_NAME, self::DB_USER, self::DB_PASSWORD);
				
	}
	
	/**
	 * Clear the database
	 */
	public static function clear() {
		$request	= ('TRUNCATE Poster;');
		
		OPDatabase::database()->query($request);
		
	}
	
	/**
	* -------------------------------------------------------------------------
	* 									Static Getters
	* -------------------------------------------------------------------------
	*/
	
	/**
	 * Return the database
	 */
	public static function database() {
		if (null == self::$database) {
			self::connect();
			
		}
		
		return self::$database;
		
	}
	
	/**
	* -------------------------------------------------------------------------
	* 								Database Save Requests
	* -------------------------------------------------------------------------
	*/
	
	/**
	* Insertion les pixels dans la base de données
	*/
	public static function savePixels() {
		for ($y=1; $y<=500; $y++) {
			for ($x=1; $x<=500; $x++) {
				$request = "INSERT INTO `Pixel` (`x`, `y`)
												VALUES (". $x .",
														". $y .");";
	
	
	
	
				self::database()->query($request);
	
	
			}
				
	
		}
	
	
	}
	
	/**
	* Save a poster into the database
	*
	* @param $poster
	*/
	public static function savePoster($poster) {
		$request = '';
		if (null == ($id = $poster->id())) {
			$request = "INSERT INTO `Poster` (`x`, `y`, `datetime`, `filename`, `id_team`)
							VALUES (". $poster->x() .",
									". $poster->y() .",
									'". $poster->dateTime() ."',
									'". $poster->fileName() ."',
									". $poster->team() .");";
	
		}else {
			$request = "UPDATE `Poster` SET
							`x` = ". $poster->x() .",
							`y`= ". $poster->y() .",
							`team` = ". $poster->team() .",
							`filename` = '". $poster->fileName() ."
							WHERE `id` = ". $id .";";
			
		}
		
		self::database()->query($request);
	
	}

	
	/**
	 * Save pixels linked to the poster
	 * 
	 * @param $poster
	 */
	public static function savePostersPixels($poster){
		$posterX	= $poster->x();
		$posterY 	= $poster->y();
		$width 		= Poster::WIDTH;
		$height 	= Poster::HEIGHT;
		
		$requestIds = "SELECT id_pixel 
							FROM Pixel 
							WHERE 		
									x>= ". $posterX ." 
								AND x<  ". ($posterX + $width) . "
								AND y>= ". $posterY ."
								AND y<  ". ($posterY + $height) .";";
		
		$ids = self::database()->query($requestIds)->fetchAll();
		
		$requestPrepared = "UPDATE Pixel 
							SET id_team = :team
							WHERE id_pixel = :id_pixel;";
		$request = self::database()->prepare($requestPrepared, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
		
		foreach ($ids as $id) {
			$request->execute(array(':team' => $poster->team(), 'id_pixel' => $id['id_pixel']));
			
		}
				
	}

	
	/**
	* -------------------------------------------------------------------------
	* 								Database Select Requests
	* -------------------------------------------------------------------------
	*/

	/**
	* Get the poster corresponding to the X Y in the database
	*
	* @var $x, $y
	* @return Poster
	*/
	public static function getPoster($x, $y) {
		$request 	= 'SELECT * FROM Poster WHERE (`x` = ' . $x . ' AND `y`= '. $y .');';
		
		$result 	= self::database()->query($request);
		$poster 	= $result->fetch();
		
		$newPoster 	= Poster::newWithIdXYTeamAndFileName($poster['id_poster'], $poster['x'], $poster['y'], $poster['id_team'], $poster['filename']);
	
		return $newPoster;
	
	}
	
	/**
	* Get all the posters ordered by date time in the database
	*
	* @return array<Poster>
	*/
	public static function allPosters() {
		$request = 'SELECT * FROM Poster ORDER BY `dateTime`;';
	
		$posters = self::database()->query($request);
		
		$postersArray = null;
		foreach ($posters->fetchAll() as $poster) {
			$newPoster		= Poster::newWithIdXYTeamAndFileName($poster['id'], $poster['x'], $poster['y'], $poster['team'], $poster['filename']);
			
			$postersArray[] = $newPoster;
			
		}
		
		return $postersArray;
	
	}
	
	/**
	* Get all the posters after the specified dateTime
	*
	* @param $dateTime
	* @return array<Poster>
	*/
	public static function getPostersAfter($dateTime) {
		$request = "SELECT * FROM Poster WHERE `dateTime` >= '". $dateTime ."' ORDER BY `dateTime`;";
		
		$posters = self::database()->query($request);
		
		$postersArray = null;
		foreach ($posters->fetchAll() as $poster) {
			$newPoster		= Poster::newWithIdXYTeamAndFileName($poster['id'], $poster['x'], $poster['y'], $poster['team'], $poster['filename']);
			
			$postersArray[] = $newPoster;
			
		}
		
		return $postersArray;
	
	}
	
	/**
	 * Select all teams into the databse
	 * 
	 * @return array<Team>
	 */
	public static function allTeams() {
		$request = "SELECT * FROM Team;";
		$teamsArray = self::database()->query($request)->fetchAll();
		
		$teams = null;
		foreach($teamsArray as $teamArray) {
			$team = Team::newWithIdAndColor($teamArray['id_team'], $teamArray['color']);
			
			$teams[] = $team;
			
		}
		
		return $teams;
		
	}
	
	/**
	 * Select a random team into the database
	 * 
	 * @return Team
	 */
	public static function randomTeam(){
		$request = "SELECT * FROM Team;";
		
		$teams = self::database()->query($request)->fetchAll();
		
		$randomTeamNumber = rand(0,count($teams)-1);
		
		$randomTeam = $teams[$randomTeamNumber];
		
		$randomTeam = Team::newWithIdAndColor($randomTeam['id_team'], $randomTeam['color']);
		
		return $randomTeam;
		
	}
	
	/**
	* -------------------------------------------------------------------------
	* 								Database Calcul Requests
	* -------------------------------------------------------------------------
	*/
	
	/**
	 * Calculates occupation for the specified team
	 * 
	 * @param $team
	 * @return percentage
	 */
	public static function occupationForTeam($team) {
		$request = "SELECT COUNT(id_pixel) FROM Pixel WHERE `id_team` = ". $team;
		
		$occupation = self::database()->query($request);
		
		$occupation	= $occupation->fetch();
		$occupation = $occupation['COUNT(id_pixel)']/(Poster::WALL_HEIGHT * Poster::WALL_WIDTH)*100;
		
		return $occupation;
		
	}

	

}

?> 